
//import java.io.UnsupportedEncodingException;
import java.sql.*;                    // java.sql is required for JDBC
import java.util.*;
/*import org.apache.commons.lang.StringEscapeUtils;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.xml.security.utils.Base64;
import org.apache.commons.logging.*;*/


public class DataSource
{
private Connection con = null; /** a connection to the database       */
private Statement stmt = null; /** a statement used to query the database */

private String databaseURL; /** URL of database                       */
private String userName;    /** user name for logging into database   */
private String password;    /** password for logging into database    */

private int limit = 100;
@SuppressWarnings("unchecked")
private ArrayList emails = null;
@SuppressWarnings("unchecked")
private ArrayList failedEmails = null;
//private ArrayList invalidEmails = null;
private ArrayList notTreatedEmails = null;
MailMessage topMessage = new MailMessage();


/**
 * load drivers using a static block
 */
static
{
  	try  {
  		Class.forName("com.mysql.jdbc.Driver"); 	}
    catch(ClassNotFoundException nf) {
        System.out.println("Could not find one of the drivers: " + nf);
    }
}

public DataSource(String _dbURL, String _userName, String _password) {
    databaseURL = _dbURL;
    userName = _userName;
    password = _password;
   
}

public boolean hasCon() {
	if (con!=null)
		return true;
	return false;
}

private void init() throws Exception {
	try {
		con = DriverManager.getConnection(databaseURL,userName,password);
		stmt = con.createStatement();
	} catch(Exception e) {
		stmt.close();  con.close(); throw e;
	} finally {
	    try   {
	         // stmt.close();  con.close();
	    } catch(Exception e) { }
	}
	
}

private void keepAlive() throws Exception {
	System.out.println("DataSource: [keepAlive()]");
	//test statement
	String query = "select 1";	
	try {
		stmt.executeQuery(query);
	} catch (Exception e) {
		System.out.println("Trying to (re)init the DB connection...\n");
		try {
			init();
		} catch (Exception exc) {
			System.out.println("Re-init failed.");
			throw exc;
		}
	} 
	
}

public void close() throws SQLException {

	try {				
		
		stmt.close(); con.close();
	} catch (SQLException e) {
		System.out.println(e.toString());
		throw e;
	} 
}

public boolean nextTopMessage() throws Exception {		
	System.out.println("DataSource: [nextTopMessage()]");
	ResultSet res = null;
	boolean newMessage = false;
	String query = "select id, subject, body from my_messages where id in"+
					"(select distinct(message_id) from my_queue where status like 'R')"+
					"order by priority desc limit 1";	
	
	try {
		keepAlive();
		res = stmt.executeQuery(query);		
    	while(res.next()) {
    		newMessage = true;
    		topMessage.setId(res.getInt("id"));
    		topMessage.setSubject(res.getString("subject"));
    		topMessage.setBody(res.getString("body"));			
    	}
	} catch(Exception e) {
		stmt.close();  con.close(); throw e;
	} finally {
	    try {
	    	res.close();  //stmt.close();  con.close();
	    } catch(Exception e) { }
	}
	
	//if (!newMessage) { topMessage = null; }
	
	return newMessage;
}

@SuppressWarnings("unchecked")
public boolean nextGroupForTopMessage() throws Exception {
	System.out.println("DataSource: [nextGroupForTopMessage()]");
	String str = null;
	ResultSet res = null;
	emails = new ArrayList();
	failedEmails = new ArrayList();
	notTreatedEmails = new ArrayList();	
	int message_id = topMessage.getId();
	
	String query = 	"select me.email as email "+  
					"from my_queue mq, my_emails me "+
					"where mq.email_id = me.id "+
					"and mq.message_id like '"+message_id+"' "+
					"and mq.status like 'R' "+
					"limit "+limit;
	
	 try  {
		 	//Properties props = new Properties();

		 	keepAlive();
			res = stmt.executeQuery(query);
			System.out.println(query);
	    	while(res.next()) {
	    		str = res.getString("email");
	    		emails.add(str);
	    		notTreatedEmails.add(str);
	        }
	    	
	    	String activeGroup = prepareQueryString(emails);
	    	
	    	String treatListQuery =	"update my_queue set status = 'T' "+
									"where message_id like '"+message_id+"' "+
									"and email_id in ( select id from my_emails where email in (" +activeGroup+ "))";
	    	
		 	keepAlive();
			stmt.executeUpdate(treatListQuery);
			System.out.println(treatListQuery);

		} catch(Exception e) {
			stmt.close();  con.close(); throw e;
		} finally {
		    try {
		    	res.close();  //stmt.close();  con.close();
		    } catch(Exception e) { }
		}
	
	return !emails.isEmpty();
}

@SuppressWarnings("unchecked")
public void addFailedEmail(String email) {
	failedEmails.add(email);	
}

@SuppressWarnings("unchecked")
public void addTreatedEmail(String email) {
	notTreatedEmails.remove(email);	
}

public void clearActiveGroupFromQueue() throws Exception {
	
	ResultSet res = null;
	String activeGroup = null;
	String failedGroup = null;
	String notTreatedGroup = null;
	int message_id = topMessage.getId();
	
	activeGroup = prepareQueryString(emails);

	System.out.println(activeGroup);
	System.out.println(failedGroup);
	System.out.println(notTreatedGroup);

	if (emails.size()>0) {			
	
		try  { 
			keepAlive();	
			if (failedEmails.size()>0)  {				
				failedGroup = prepareQueryString(failedEmails);
				String failedQuery = 	"update my_queue set status = 'F' "+
										"where message_id like '"+message_id+"' "+
										"and email_id in ( select id from my_emails where email in (" +failedGroup+ "))";				
				stmt.executeUpdate(failedQuery);
				System.out.println(failedQuery);
			}			
			
			keepAlive();
			//slightly clean the T-mess up
			if (notTreatedEmails.size()>0)  {	
				notTreatedGroup = prepareQueryString(notTreatedEmails);
				String treatedQuery = 	"update my_queue set status = 'R' "+
								"where message_id like '"+message_id+"' "+
								//"and status like 'T' "+
								"and email_id in ( select id from my_emails where email in (" +notTreatedGroup+ "))";
				stmt.executeUpdate(treatedQuery);				
				System.out.println(treatedQuery);
			}
		
			keepAlive();			
			String query = 	"update my_queue set status = 'D' "+
							"where message_id like '"+message_id+"' "+
							"and status like 'T' "+
							"and email_id in ( select id from my_emails where email in (" +activeGroup+ "))";	
			stmt.executeUpdate(query);
			System.out.println(query);
			
		} catch(Exception e) {
			stmt.close();  con.close(); throw e;
		} finally {
			try {
				res.close();  //stmt.close();  con.close();
			} catch(Exception e) { }
		}
		
	}
}

public MailMessage getTopMessage() {
	return topMessage;
}

@SuppressWarnings("unchecked")
public ArrayList getEmailQueue() {
	return emails;	
}

public int getQueueSize() throws Exception {
	int queueSize = 0;
	ResultSet res = null;
	String query = "select count(*) as queueSize from my_queue where status like 'R'";	
	
	try {
		keepAlive();
		res = stmt.executeQuery(query);
		
    	while(res.next()) {
			queueSize = res.getInt("queueSize");
    	}
		
	} catch(Exception e) {
		stmt.close();  con.close(); throw e;
	} finally {
	    try   {
	          res.close();  //stmt.close();  con.close();
	    } catch(Exception e) { }
	}
	
	return queueSize;
}

private String prepareQueryString(List list) {
	
	String result = null;
	
	if (list.size()>0) {
		result = "'";
		for (int i=0; i<list.size()-1;i++) {
			result += list.get(i) + "', '";
		}
		result += list.get(list.size()-1) + "'";
	}
	
	return result;
}

public boolean resetFailedEmails() throws Exception {
	int res;
	boolean newMessage = false;

	String query = "update my_queue set status = 'R' "+
					"where status like 'F'";	
	
	try {
		keepAlive();
		res = stmt.executeUpdate(query);
		
		if (res>0) newMessage = true;
		
	} catch(Exception e) {
		stmt.close();  con.close(); throw e;
	} 
			
	return newMessage;
}

/*
 * 
 * 	    		//byte buffer[] = res.getBytes("body");
	    		//System.out.write(buffer);
	    		//byte[] utf8_buffer = Base64.decode(buffer);
	    		//byte[] utf8_buffer = UnicodeUtils.convert(buffer, "UTF-8");
	    		//System.out.println("\nbase64-----------");
	    		//System.out.write(utf8_buffer);
				//body = new String(buffer);//,"ISO8859_1");
				//System.out.println(body);

	    		//bodies.add(StringEscapeUtils.unescapeHtml(body));
private String convertBody(int message_id) {
	//System.out.println("Retrieving "+message_id);
	String requestUrl = "http://autobelgium.ru/modules.php?name=Submit_News&op=fetchBody&message_id="+message_id;
    Byte[] sb ;
    try {
        URL url = new URL(requestUrl.toString());
        
        // Obtain output stream
        InputStream is = url.openStream();
        
        FileOutputStream fileOutput = 
			new FileOutputStream("D:\\test.html");	
        
        // Read and display data from url
        byte buffer[] = new byte[1024];
        int i;
        while ((i = is.read(buffer)) != -1) {
        	//fileOutput.write(buffer);
            //System.out.write(buffer, 0, i);
        }
        
		fileOutput.flush();
		fileOutput.close();

        System.out.println("-----RESPONSE END-----");
        
    } catch (IOException e) {
        e.printStackTrace();
    }
    
    return "snd"; //new String(sb., "UTF-8");
}
*/


}
